package nc.bs.swjw.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nc.bs.framework.common.NCLocator;
import nc.bs.swjw.util.queryParamHandlerUtil;
import nc.vo.pub.lang.UFDouble;
import nccloud.pubitf.gl.account.IAccountReportWebService;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import nc.bs.dao.BaseDAO;
import nc.bs.swjw.itf.ISwjwExcelImportService;
import nc.jdbc.framework.processor.MapListProcessor;
import nc.vo.am.common.util.CollectionUtils;
import nc.vo.am.excel.util.POIExcel;
import nc.vo.pub.BusinessException;
import nccloud.pubitf.ampub.common.excelimport.ImportFile;
import uap.lfw.bq.portal.base.StringUtils;

public class SwjwExcelImportServiceImpl implements ISwjwExcelImportService {
	private IAccountReportWebService accountReportWebService;

	@Override
	public ImportFile swjwExcelImport(String accountBook, String queryDate) throws Exception {
		Workbook workbook = POIExcel.createExportExcel();

		List<Map<String, Object>> beginMonthData = queryMonthData(queryDate);
		String[] yearmonth = queryDate.split("-");
		String year = yearmonth[0];
		String month = yearmonth[1];

		//
//		year="2024";
//		month="05";
//		accountBook="1001A210000000000R84";
		//
		// 会计科目
		String[] excelColItems1 = { "kmbh", "km_name", "zjm", "grade", "HasSon", "km_fzhs", "km_sx", "km_lname" };
		List<Map<String, Object>> vos1 = this.queryAccasoaData(accountBook);
		doExport(workbook, "会计科目", excelColItems1, vos1);

		// 科目余额
		String[] excelColItems2 = { "year", "month", "kmbh", "km_nc", "km_ljf", "km_ldf", "km_jf", "km_df", "km_qm" };
		List<Map<String, Object>> vos2 = queryAccasoaBanlanceData(accountBook, year, month);
		doExport(workbook, "科目余额", excelColItems2, vos2);

		// 辅助余额1
		String[] excelColItems3 = { "year", "month", "kmbh", "KM_FZHS", "Fzdm", "km_nc", "km_ljf", "km_ldf", "km_jf",
				"km_df", "km_qm" };
		List<Map<String, Object>> vos3 = queryAssistBanlanceData(accountBook, year, month);
		doExport(workbook, "辅助余额1", excelColItems3, vos3);

		// 会计凭证1
		String[] excelColItems4 = { "year", "month", "day", "pzbh", "ZY", "kmbh", "jf", "df", "pzrow", "Fjzs", "IsJzpz",
				"Zdr", "CheckMan", "JzMan", "CwZg" };
		List<Map<String, Object>> vos4 = queryVoucherData(accountBook, year, month);;
		doExport(workbook, "会计凭证1", excelColItems4, vos4);
		// 辅助凭证1
		String[] excelColItems5 = { "year", "month", "day", "pzbh", "ZY", "kmbh", "jf", "df", "pzrow", "km_fzhs","FZDM" };
		List<Map<String, Object>> vos5 = this.queryAssistVoucherData(accountBook, year, month);
		doExport(workbook, "辅助凭证1", excelColItems5, vos5);
		// 凭证差异分析1
		String[] excelColItems6 = { "year", "month", "pzbh", "ZY", "lx", "fzdm", "fz_name", "jf", "df" };
		List<Map<String, Object>> vos6 = this.queryDiffVoucherData(accountBook, year, month);
		doExport(workbook, "凭证差异分析1", excelColItems6, vos6);
		// 部门数据字典
		String[] excelColItems7 = { "BH", "TheName", "Zjm", "Grade", "IsLeaf", "Parent" };
		List<Map<String, Object>> vos7 = this.queryDeptData();
		doExport(workbook, "部门数据字典", excelColItems7, vos7);

		// 往来数据字典
		String[] excelColItems8 = { "BH", "TheName", "Zjm", "Grade", "IsLeaf", "Parent" };
		List<Map<String, Object>> vos8 = this.querywlData();
		doExport(workbook, "往来数据字典", excelColItems8, vos8);

		// 项目数据字典
		String[] excelColItems9 = { "BH", "TheName", "Zjm", "Grade", "IsLeaf", "Parent" };
		List<Map<String, Object>> vos9 = this.queryxmData();
		doExport(workbook, "项目数据字典", excelColItems9, vos9);

		// 功能数据字典
		String[] excelColItems10 = { "BH", "TheName", "Zjm", "Grade", "IsLeaf", "Parent" };
		List<Map<String, Object>> vos10 = this.querygnData();
		doExport(workbook, "功能数据字典", excelColItems10, vos10);

		InputStream inputStream = null;

		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		try {
			POIExcel.writeXlsFile(workbook, outputStream);
			inputStream = new ByteArrayInputStream(outputStream.toByteArray());
			outputStream.close();
		} catch (Throwable throwable) {
			try {
				outputStream.close();
			} catch (Throwable throwable1) {
				throwable.addSuppressed(throwable1);
			}
		}

		ImportFile importFile = new ImportFile();
		importFile.setFileName("省卫健委相关数据_" + queryDate + "_temp.xls");
		importFile.setInputStream(inputStream);
		return importFile;
	}

	// 公共生成excel
	private void doExport(Workbook workbook, String sheetName, String[] excelColItems, List<Map<String, Object>> vos)
			throws BusinessException {
		Sheet sheet = null;
//		if (POIExcel.isUsing(workbook)) {
//			throw new BusinessException(
//					NCLangRes4VoTransl.getNCLangRes().getStrByID("ampubnccloud_0", "04501008-0018"));
//		}

		sheet = POIExcel.createNewSheet(workbook, sheetName);

		CellStyle commStyle = workbook.createCellStyle();

		CellStyle requireStyle = workbook.createCellStyle();
		DataFormat format = workbook.createDataFormat();
		commStyle.setDataFormat(format.getFormat("@"));
		requireStyle.setDataFormat(format.getFormat("@"));

		Font font = workbook.createFont();
		font.setColor((short) 10);
		requireStyle.setFont(font);

		for (int i = 0; i < excelColItems.length; i++) {
			String name = excelColItems[i];
			POIExcel.setCellValue(sheet, 0, i, name, commStyle);
		}

		String value = null;
		if (vos != null && vos.size() > 0) {
			for (int i = 0; i < vos.size(); i++) {
				for (int j = 0; j < excelColItems.length; j++) {
					Object obj = vos.get(i).get(excelColItems[j].toLowerCase());
					value = (obj == null) ? null : obj.toString();
					POIExcel.setCellValue(sheet, i + 1, j, value, commStyle);
				}
			}
		}
	}
	/**
	 * 	会计月查询
	 * @param monthpk
	 * @return
	 * @throws BusinessException
	 */
	private List<Map<String, Object>> queryMonthData(String monthpk)throws BusinessException  {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT bd_accperiodmonth.pk_accperiodmonth nmonthpk, \n");
		sql.append("       bd_accperiod.periodyear|| '-'||bd_accperiodmonth.accperiodmth nmonth \n");
		sql.append("  FROM bd_accperiodmonth \n");
		sql.append("  LEFT JOIN bd_accperiod\n");
		sql.append("    ON bd_accperiodmonth.pk_accperiod = bd_accperiod.pk_accperiod \n");
		sql.append(" WHERE 1 = 1 \n");

		if (StringUtils.isNotBlank(monthpk)) {
			sql.append("   AND bd_accperiodmonth.pk_accperiodmonth = '" + monthpk + "' \n");// 会计期间
		}

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}
		return sqlResult;
	}



	/**
	 * 会计科目查询
	 *
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryAccasoaData(String accountBook) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT bd_account.code kmbh, \n");
		sql.append("       bd_account.name km_name, \n");
		sql.append("       bd_account.remcode zjm, \n");
		sql.append("       bd_account.acclev grade, \n");
		sql.append("       CASE WHEN bd_accasoa.endflag = 'Y' THEN 1 ELSE 0 END HasSon, \n");
		sql.append("       bd_acctype.code km_fzhs, \n");
		sql.append("       bd_account.balanorient km_sx, \n");
		sql.append("       bd_accasoa.dispname km_lname \n");
		sql.append("  FROM bd_accasoa \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append("  LEFT JOIN bd_acctype ON bd_account.pk_acctype = bd_acctype.pk_acctype \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND bd_accasoa.pk_accchart = '1001A110000000APACXV' \n"); // 只取医院科目体系的科目
		sql.append("   AND bd_accasoa.enablestate = 2 \n");
		sql.append("   AND bd_accasoa.dr = 0 \n");
		sql.append("   AND bd_account.dr = 0 \n");
		sql.append("   AND bd_account.enablestate = 2 \n");
		sql.append(" ORDER BY bd_account.code ASC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	/**
	 * 科目余额查询
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryAccasoaBanlanceData(String accountBook, String year, String month) throws BusinessException {
		Map<String, Object> map = new HashMap<>();
		map.put("pk_accountingbook",accountBook);
		map.put("year",year);
		LocalDate firstDayOfMonth = LocalDate.of(Integer.valueOf(year), Integer.valueOf(month), 1);
		String lastDayOfMonth = firstDayOfMonth.withDayOfMonth(firstDayOfMonth.lengthOfMonth())+" 23:59:59";
		map.put("month",month);
		map.put("startdate",firstDayOfMonth);
		map.put("enddate", lastDayOfMonth);
		Map<String, Object> param = queryParamHandlerUtil.getKMYEQueryParam(map);
		Map<String, Object> mapdata = this.getAccountReportWebService().queryAccBalance(param);
		if(mapdata.isEmpty()){
			return null;
		}
		List<Map<String,Object>> res =  (List<Map<String,Object>>)mapdata.get("data");
		if(CollectionUtils.isEmpty(res)){
			return null;
		}
		List<Map<String, Object>> sqlResult =  new ArrayList<Map<String, Object>>();
		for(int i = 0 ; i< res.size(); i++){
			if(i == res.size()) continue;// 最后一条是合计
			Map<String, Object> r = res.get(i);
			HashMap<String, Object> rmap = new HashMap<>();
			rmap.put("year",r.get("year"));
			rmap.put("kmbh",r.get("acccode"));
			rmap.put("month",month);
			UFDouble km_nc = r.get("initlocamount") == null ? new UFDouble(0): new UFDouble(r.get("initlocamount").toString().replace(",",""));
			rmap.put("km_nc", km_nc);
			UFDouble km_ljf = r.get("debitaccumlocamount") == null ? new UFDouble(0): new UFDouble(r.get("debitaccumlocamount").toString().replace(",",""));
			rmap.put("km_ljf", km_ljf);
			UFDouble km_ldf = r.get("creditaccumlocamount") == null ? new UFDouble(0): new UFDouble(r.get("creditaccumlocamount").toString().replace(",",""));
			rmap.put("km_ldf", km_ldf);
			UFDouble km_jf = r.get("debitlocamount") == null ? new UFDouble(0): new UFDouble(r.get("debitlocamount").toString().replace(",",""));
			rmap.put("km_jf", km_jf);
			UFDouble km_df = r.get("creditlocamount") == null ? new UFDouble(0): new UFDouble(r.get("creditlocamount").toString().replace(",",""));
			rmap.put("km_df", km_df);
			UFDouble km_qm = r.get("endlocamount") == null ? new UFDouble(0): new UFDouble(r.get("endlocamount").toString().replace(",",""));
			rmap.put("km_qm", km_qm);
			sqlResult.add(rmap);
		}
		return sqlResult;
	}

	/**
	 * 辅助余额查询
	 *
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryAssistBanlanceData(String accountBook, String year, String month) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT '" + year + "' year, \n");
		sql.append("       '" + month + "' month, \n");
		sql.append("       kmbh kmbh, \n");
		sql.append("       KM_FZHS KM_FZHS, \n");
		sql.append("       Fzdm Fzdm, \n ");
		sql.append("       initdebitamount-initcreditamount  km_nc, \n");
		sql.append("       enddebitamount km_ljf, endcreditamount km_ldf, \n");
		sql.append("       occurdebitamount km_jf, \n");
		sql.append("       endcreditamount km_df, \n");
		sql.append("       enddebitamount-endcreditamount km_qm \n");
		sql.append("  FROM (  \n");

		// 部门 org_dept F1
		sql.append(appendDefdocSql(accountBook, year, month, "org_dept", "F1", "0001", Boolean.TRUE ));
		// 人员 bd_psndoc F2
		sql.append(appendDefdocSql(accountBook, year, month, "bd_psndoc", "F2", "0002", Boolean.FALSE ));
		// 现金流量项目 bd_cashflow F7
		sql.append(appendDefdocSql(accountBook, year, month, "bd_cashflow", "F7", "0007", Boolean.FALSE ));
		// 客户 bd_customer F13
		sql.append(appendDefdocSql(accountBook, year, month, "bd_customer", "F13", "0017", Boolean.FALSE ));
		// 供应商 bd_supplier F14
		sql.append(appendDefdocSql(accountBook, year, month, "bd_supplier", "F14", "0018", Boolean.FALSE ));
		// 资金来源 bd_defdoc F18
		sql.append(appendDefdocF18Sql(accountBook, year, month, "bd_defdoc", "F18", "HRP002", Boolean.FALSE ));
		// 支付对象 bd_defdoc F19
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F19", "HRP003", Boolean.FALSE ));
		// 指令性任务 bd_defdoc F20
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F20", "HRP001", Boolean.FALSE ));
		// 收入来源 bd_defdoc F21
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F21", "HRP008", Boolean.FALSE ));
		// 拨款种类 bd_defdoc F22
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F22", "HRP004", Boolean.FALSE ));
		// 支出功能分类科目 bd_defdoc F23
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F23", "HRP-ZCGNFL", Boolean.FALSE ));
		// 项目名称 bd_defdoc F24
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F24", "HRP005", Boolean.FALSE ));
		// 项目类型 bd_defdoc F25
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F25", "HRP006", Boolean.FALSE ));
		// 基建项目 bd_defdoc F26
		sql.append(appendDefdocSql(accountBook, year, month, "bd_defdoc", "F26", "HRP007", Boolean.FALSE ));

		sql.append("  ) temp ORDER BY kmbh, KM_FZHS ASC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	private StringBuffer appendDefdocSql(String accountBook, String year, String month,String tableName, String fieldName, String fzhscode, Boolean first) {
		String pkName = tableName;
		if(tableName.indexOf("bd") >= 0) {
			pkName = tableName.replace("bd", "pk");
		}else {
			pkName = tableName.replace("org", "pk");
		}
		StringBuffer sql = new StringBuffer();
		if(!first) {
			sql.append(" UNION ALL \n");
		}
		sql.append(" select ");
		// modify by wy
		sql.append(" aav.code kmbh,");
		sql.append("       '" + fzhscode + "' KM_FZHS, \n"); // 科目辅助核算编码
		sql.append("       "+ tableName +".code Fzdm, \n");// 供应商编码
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod >= '00' and adjustperiod < '" + month
				+ "' then debitamount else 0 end ) initdebitamount,  \n");
		sql.append("       sum ( case when adjustperiod <> '00' and year || adjustperiod >= '" + year + month
				+ "' and year || adjustperiod <= '" + year + month
				+ "' then debitamount else 0 end ) occurdebitamount, \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod <= '" + month
				+ "' then debitamount else 0 end ) enddebitamount, \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod >= '00' and adjustperiod < '" + month
				+ "' then creditamount else 0 end ) initcreditamount,  \n");
		sql.append("       sum ( case when adjustperiod <> '00' and year || adjustperiod >= '" + year + month
				+ "' and year || adjustperiod <= '" + year + month
				+ "' then creditamount else 0 end ) occurcreditamount,  \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod <= '" + year
				+ "' then creditamount else 0 end ) endcreditamount \n");
		sql.append("  FROM gl_balance gl_balan  \n");
		sql.append(" inner join Account_Accasoa_View aav on gl_balan.PK_ACCASOA = aav.PK_ACCASOA ");
		sql.append(" INNER JOIN (SELECT " + fieldName + ",assid,f18 FROM gl_docfree1 where " + fieldName
				+ " is not null AND " + fieldName + " !='NN/A')gl_docfree1 ON gl_balan.assid = gl_docfree1.assid \n");// 辅助核算F1
		sql.append(" INNER JOIN "+ tableName +" ON gl_docfree1." + fieldName + " = "+ tableName +"."+ pkName +" \n");// 供应商
		sql.append(" INNER JOIN bd_accasoa ON gl_balan.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" LEFT JOIN bd_defdoc doc ON gl_docfree1.f18 = doc.pk_defdoc \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_balan.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目
		sql.append("   AND gl_balan.voucherkind <> 5 \n");
		sql.append("   AND gl_balan.voucherkind <> 4 \n");
		sql.append("   AND gl_balan.dr = 0 \n");
		sql.append("   AND gl_balan.year >= '" + year + "' \n");
		sql.append("   AND gl_balan.year || gl_balan.adjustperiod <= '" + year + month + "' \n");
		// 辅助核算维度统计
		sql.append("  GROUP BY \n");
		sql.append("aav.code, ");
		sql.append(tableName +".code");

		return sql;
	}
	private StringBuffer appendDefdocF18Sql(String accountBook, String year, String month,String tableName, String fieldName, String fzhscode, Boolean first) {
		String pkName = tableName;
		if(tableName.indexOf("bd") >= 0) {
			pkName = tableName.replace("bd", "pk");
		}else {
			pkName = tableName.replace("org", "pk");
		}
		StringBuffer sql = new StringBuffer();
		if(!first) {
			sql.append(" UNION ALL \n");
		}
		sql.append(" select ");
		// modify by wy
		sql.append(" aav.code kmbh,");
		sql.append("       '" + fzhscode + "' KM_FZHS, \n"); // 科目辅助核算编码
		sql.append("       "+ tableName +".code Fzdm, \n");// 供应商编码
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod >= '00' and adjustperiod < '" + month
				+ "' then debitamount else 0 end ) initdebitamount,  \n");
		sql.append("       sum ( case when adjustperiod <> '00' and year || adjustperiod >= '" + year + month
				+ "' and year || adjustperiod <= '" + year + month
				+ "' then debitamount else 0 end ) occurdebitamount, \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod <= '" + month
				+ "' then debitamount else 0 end ) enddebitamount, \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod >= '00' and adjustperiod < '" + month
				+ "' then creditamount else 0 end ) initcreditamount,  \n");
		sql.append("       sum ( case when adjustperiod <> '00' and year || adjustperiod >= '" + year + month
				+ "' and year || adjustperiod <= '" + year + month
				+ "' then creditamount else 0 end ) occurcreditamount,  \n");
		sql.append("       sum ( case when year = '" + year + "' and adjustperiod <= '" + year
				+ "' then creditamount else 0 end ) endcreditamount \n");
		sql.append("  FROM gl_balance gl_balan  \n");
		sql.append(" inner join Account_Accasoa_View aav on gl_balan.PK_ACCASOA = aav.PK_ACCASOA \n ");
		sql.append(" INNER JOIN (SELECT " + fieldName + ",assid FROM gl_docfree1 where " + fieldName
				+ " is not null AND " + fieldName + " !='NN/A')gl_docfree1 ON gl_balan.assid = gl_docfree1.assid \n");// 辅助核算F1
		sql.append(" INNER JOIN "+ tableName +" ON gl_docfree1." + fieldName + " = "+ tableName +"."+ pkName +" \n");// 供应商
		sql.append(" INNER JOIN bd_accasoa ON gl_balan.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" LEFT JOIN bd_defdoc doc ON gl_docfree1.f18 = doc.pk_defdoc \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_balan.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目
		sql.append("   AND gl_balan.voucherkind <> 5 \n");
		sql.append("   AND gl_balan.voucherkind <> 4 \n");
		sql.append("   AND gl_balan.dr = 0 \n");
		sql.append("   AND gl_balan.year >= '" + year + "' \n");
		sql.append("   AND gl_balan.year || gl_balan.adjustperiod <= '" + year + month + "' \n");
		// 辅助核算维度统计
		sql.append("  GROUP BY \n");
		sql.append(" aav.code, ");
		sql.append(tableName +".code");

		return sql;
	}

	/**
	 * 	会计凭证查询
	 * @param year
	 * @param month
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryVoucherData(String accountBook, String year, String month) throws BusinessException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT gl_detail.yearv year, \n");
		sql.append("       gl_detail.periodv month, \n");
		sql.append("       extract(day from to_date(prepareddate, 'yyyy-MM-dd HH24:mi:ss')) day, \n");
		sql.append("       gl_voucher.num pzbh, \n");
		sql.append("       gl_detail.explanation ZY,\n");
		sql.append("       aav.code kmbh, \n");
		sql.append("       gl_detail.debitamount jf, \n");
		sql.append("       gl_detail.creditamount df, \n");
		sql.append("       gl_detail.detailindex pzrow, \n");
		sql.append("       gl_voucher.attachment Fjzs, \n");
		sql.append("       gl_voucher.voucherkind IsJzpz, \n");
		sql.append("       user1.user_name Zdr, \n");
		sql.append("       user2.user_name CheckMan, \n");
		sql.append("       user3.user_name JzMan , \n");
		sql.append("       '刘凤娟'  CwZg \n");
		sql.append("  FROM gl_detail  \n");
		sql.append(" INNER JOIN gl_voucher ON gl_detail.pk_voucher = gl_voucher.pk_voucher  \n");
		sql.append(" INNER JOIN bd_accasoa ON gl_detail.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" inner join Account_Accasoa_View aav on bd_accasoa.PK_ACCASOA = aav.PK_ACCASOA  \n");
		sql.append(" LEFT JOIN gl_docfree1 ON gl_docfree1.assid = gl_detail.assid \n");
		sql.append(" INNER JOIN bd_defdoc ON gl_docfree1.F18 = bd_defdoc.pk_defdoc");
		sql.append(" INNER JOIN sm_user user1 ON gl_voucher.pk_prepared = user1.cuserid   \n");
		sql.append(" INNER JOIN sm_user user2 ON gl_voucher.pk_checked = user2.cuserid \n");
		sql.append(" INNER JOIN sm_user user3 ON gl_voucher.pk_prepared = user3.cuserid \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_voucher.approvestatus = 1 \n");
		sql.append("   AND gl_detail.dr = 0 \n");
		sql.append("   AND gl_voucher.dr = 0 \n");
		sql.append("   AND gl_voucher.year = '" + year + "' \n");
		sql.append("   AND gl_voucher.period = '" + month + "' \n");
		sql.append("   AND gl_voucher.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目
		sql.append(" ORDER BY extract(day from to_date(prepareddate, 'yyyy-MM-dd HH24:mi:ss')) DESC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	/**
	 * 	辅助凭证查询
	 * @param year
	 * @param month
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryAssistVoucherData(String accountBook, String year, String month) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ( \n");
		// 部门 org_dept F1
		sql.append(appendAssistSql(accountBook, year, month, "org_dept", "F1", "0001", Boolean.TRUE ));
		// 人员 bd_psndoc F2
		sql.append(appendAssistSql(accountBook, year, month, "bd_psndoc", "F2", "0002", Boolean.FALSE ));
		// 现金流量项目 bd_cashflow F7
		sql.append(appendAssistSql(accountBook, year, month, "bd_cashflow", "F7", "0007", Boolean.FALSE ));
		// 客户 bd_customer F13
		sql.append(appendAssistSql(accountBook, year, month, "bd_customer", "F13", "0017", Boolean.FALSE ));
		// 供应商 bd_supplier F14
		sql.append(appendAssistSql(accountBook, year, month, "bd_supplier", "F14", "0018", Boolean.FALSE ));
		// 资金来源 bd_defdoc F18
		sql.append(appendAssistSqlF18(accountBook, year, month, "bd_defdoc", "F18", "HRP002", Boolean.FALSE ));
		// 支付对象 bd_defdoc F19
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F19", "HRP003", Boolean.FALSE ));
		// 指令性任务 bd_defdoc F20
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F20", "HRP001", Boolean.FALSE ));
		// 收入来源 bd_defdoc F21
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F21", "HRP008", Boolean.FALSE ));
		// 拨款种类 bd_defdoc F22
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F22", "HRP004", Boolean.FALSE ));
		// 支出功能分类科目 bd_defdoc F23
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F23", "HRP-ZCGNFL", Boolean.FALSE ));
		// 项目名称 bd_defdoc F24
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F24", "HRP005", Boolean.FALSE ));
		// 项目类型 bd_defdoc F25
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F25", "HRP006", Boolean.FALSE ));
		// 基建项目 bd_defdoc F26
		sql.append(appendAssistSql(accountBook, year, month, "bd_defdoc", "F26", "HRP007", Boolean.FALSE ));

		sql.append(" ) temp ORDER BY day DESC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	private StringBuffer appendAssistSql(String accountBook, String year, String month, String tableName, String fieldName, String fzhscode, Boolean first) {
		String pkName = tableName;
		if(tableName.indexOf("bd") >= 0) {
			pkName = tableName.replace("bd", "pk");
		}else {
			pkName = tableName.replace("org", "pk");
		}
		StringBuffer sql = new StringBuffer();
		if(!first) {
			sql.append(" UNION ALL \n");
		}
		sql.append("SELECT gl_detail.yearv year, \n");
		sql.append("       gl_detail.periodv month, \n");
		sql.append("       extract(day from to_date(prepareddate, 'yyyy-MM-dd HH24:mi:ss')) day, \n");
		sql.append("       gl_voucher.num pzbh, \n");
		sql.append("       gl_detail.explanation ZY,\n");
		sql.append("       aav.code kmbh, \n");
		sql.append("       gl_detail.debitamount jf, \n");
		sql.append("       gl_detail.creditamount df, \n");
		sql.append("       gl_detail.detailindex pzrow, \n");
		sql.append("       '"+ fzhscode +"' km_fzhs, \n");
		sql.append("       "+ tableName +".code FZDM \n");
		sql.append("  FROM gl_detail  \n");
		sql.append(" INNER JOIN gl_voucher ON gl_detail.pk_voucher = gl_voucher.pk_voucher  \n");
		sql.append(" INNER JOIN bd_accasoa ON gl_detail.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" inner join Account_Accasoa_View aav on bd_accasoa.PK_ACCASOA = aav.PK_ACCASOA  \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" INNER JOIN (SELECT "+ fieldName +",assid,f18 FROM gl_docfree1 where "+ fieldName +" is not null AND "+ fieldName +" !='NN/A')gl_docfree1 ON gl_detail.assid = gl_docfree1.assid \n");
		sql.append(" INNER JOIN "+ tableName +" ON gl_docfree1."+ fieldName +" = "+ tableName +"."+ pkName +" \n");
		sql.append(" LEFT JOIN bd_defdoc doc ON gl_docfree1.f18 = doc.pk_defdoc \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_voucher.approvestatus = 1 \n");
		sql.append("   AND gl_detail.dr = 0 \n");
		sql.append("   AND gl_voucher.dr = 0 \n");
		sql.append("   AND gl_voucher.year = '" + year + "' \n");
		sql.append("   AND gl_voucher.period = '" + month + "' \n");
		sql.append("   AND gl_voucher.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目

		return sql;
	}
	private StringBuffer appendAssistSqlF18(String accountBook, String year, String month, String tableName, String fieldName, String fzhscode, Boolean first) {
		String pkName = tableName;
		if(tableName.indexOf("bd") >= 0) {
			pkName = tableName.replace("bd", "pk");
		}else {
			pkName = tableName.replace("org", "pk");
		}
		StringBuffer sql = new StringBuffer();
		if(!first) {
			sql.append(" UNION ALL \n");
		}
		sql.append("SELECT gl_detail.yearv year, \n");
		sql.append("       gl_detail.periodv month, \n");
		sql.append("       extract(day from to_date(prepareddate, 'yyyy-MM-dd HH24:mi:ss')) day, \n");
		sql.append("       gl_voucher.num pzbh, \n");
		sql.append("       gl_detail.explanation ZY,\n");
		sql.append("       aav.code kmbh, \n");

		sql.append("       gl_detail.debitamount jf, \n");
		sql.append("       gl_detail.creditamount df, \n");
		sql.append("       gl_detail.detailindex pzrow, \n");
		sql.append("       '"+ fzhscode +"' km_fzhs, \n");
		sql.append("       "+ tableName +".code FZDM \n");
		sql.append("  FROM gl_detail  \n");
		sql.append(" INNER JOIN gl_voucher ON gl_detail.pk_voucher = gl_voucher.pk_voucher  \n");
		sql.append(" INNER JOIN bd_accasoa ON gl_detail.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" inner join Account_Accasoa_View aav on bd_accasoa.PK_ACCASOA = aav.PK_ACCASOA  \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" INNER JOIN (SELECT "+ fieldName +",assid FROM gl_docfree1 where "+ fieldName +" is not null AND "+ fieldName +" !='NN/A')gl_docfree1 ON gl_detail.assid = gl_docfree1.assid \n");
		sql.append(" INNER JOIN "+ tableName +" ON gl_docfree1."+ fieldName +" = "+ tableName +"."+ pkName +" \n");
		//	sql.append(" LEFT JOIN bd_defdoc doc ON gl_docfree1.f18 = doc.pk_defdoc \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_voucher.approvestatus = 1 \n");
		sql.append("   AND gl_detail.dr = 0 \n");
		sql.append("   AND gl_voucher.dr = 0 \n");
		sql.append("   AND gl_voucher.year = '" + year + "' \n");
		sql.append("   AND gl_voucher.period = '" + month + "' \n");
		sql.append("   AND gl_voucher.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目

		return sql;
	}

	/**
	 * 	差异凭证分析查询
	 * @param year
	 * @param month
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryDiffVoucherData(String accountBook, String year, String month) throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ( \n");
		// 部门 org_dept F1
		sql.append(appendDiffAssistSql(accountBook, year, month, "org_dept", "F1", Boolean.TRUE ));
		// 人员 bd_psndoc F2
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_psndoc", "F2", Boolean.FALSE ));
		// 现金流量项目 bd_cashflow F7
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_cashflow", "F7", Boolean.FALSE ));
		// 客户 bd_customer F13
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_customer", "F13", Boolean.FALSE ));
		// 供应商 bd_supplier F14
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_supplier", "F14", Boolean.FALSE ));
		// 资金来源 bd_defdoc F18
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F14", Boolean.FALSE ));
		// 支付对象 bd_defdoc F19
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F19", Boolean.FALSE ));
		// 指令性任务 bd_defdoc F20
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F20", Boolean.FALSE ));
		// 收入来源 bd_defdoc F21
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F21", Boolean.FALSE ));
		// 拨款种类 bd_defdoc F22
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F22", Boolean.FALSE ));
		// 支出功能分类科目 bd_defdoc F23
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F23", Boolean.FALSE ));
		// 项目名称 bd_defdoc F24
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F24", Boolean.FALSE ));
		// 项目类型 bd_defdoc F25
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F25", Boolean.FALSE ));
		// 基建项目 bd_defdoc F26
		sql.append(appendDiffAssistSql(accountBook, year, month, "bd_defdoc", "F26", Boolean.FALSE ));


		sql.append(" ) temp ORDER BY pzbh asc \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	private StringBuffer appendDiffAssistSql(String accountBook, String year, String month, String tableName, String fieldName, Boolean first) {
		String pkName = tableName;
		if(tableName.indexOf("bd") >= 0) {
			pkName = tableName.replace("bd", "pk");
		}else {
			pkName = tableName.replace("org", "pk");
		}
		StringBuffer sql = new StringBuffer();
		if(!first) {
			sql.append(" UNION ALL \n");
		}
		sql.append("SELECT gl_detail.yearv year, \n");
		sql.append("       gl_detail.periodv month, \n");
		sql.append("       gl_voucher.num pzbh, \n");
		sql.append("       gl_voucher.voucherkind lx,\n");
		sql.append("       gl_detail.debitamount jf, \n");
		sql.append("       gl_detail.creditamount df, \n");
		sql.append("       gl_diffanalysis.name fz_name, \n");
		sql.append("       "+ tableName +".code fzdm \n");
		sql.append("  FROM gl_diffanaly  \n");
		sql.append(" INNER JOIN gl_detail ON gl_detail.pk_detail = gl_diffanaly.pk_detail   \n");
		sql.append(" INNER JOIN gl_voucher ON gl_detail.pk_voucher = gl_voucher.pk_voucher  \n");
		sql.append(" INNER JOIN bd_accasoa ON gl_detail.pk_accasoa = bd_accasoa.pk_accasoa \n");
		sql.append(" INNER JOIN bd_account ON bd_accasoa.pk_account = bd_account.pk_account \n");
		sql.append(" LEFT JOIN gl_diffanalysis ON gl_diffanaly.defcol = gl_diffanalysis.pk_diffanaly   \n");
		sql.append(" INNER JOIN (SELECT "+ fieldName +",assid FROM gl_docfree1 where "+ fieldName +" is not null AND "+ fieldName +" !='NN/A')gl_docfree1 ON gl_detail.assid = gl_docfree1.assid \n");
		sql.append(" INNER JOIN "+ tableName +" ON gl_docfree1."+ fieldName +" = "+ tableName +"."+ pkName +" \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND gl_voucher.approvestatus = 1 \n");
		sql.append("   AND gl_detail.dr = 0 \n");
		sql.append("   AND gl_voucher.dr = 0 \n");
		sql.append("   AND gl_voucher.year = '" + year + "' \n");
		sql.append("   AND gl_voucher.period = '" + month + "' \n");
		sql.append("   AND gl_voucher.pk_accountingbook IN ( '"+ accountBook +"' ) \n"); // 只取医院科目体系的科目

		return sql;
	}


	/**
	 *  	部门查询
	 * @return
	 * @throws BusinessException
	 */
	public List<Map<String, Object>> queryDeptData() throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT dept.code BH, \n");
		sql.append("       dept.name TheName, \n");
		sql.append("       dept.mnecode Zjm, \n");
		sql.append("       case when Length(dept.code) = 1 then 1 when Length(dept.code) = 3 then 2 when Length(dept.code) = 5 then 3 else 4 end  Grade, \n");
		sql.append("       CASE WHEN EXISTS (SELECT 1 FROM org_dept c WHERE c.pk_fatherorg = dept.pk_dept) THEN '0' ELSE '1' END IsLeaf, \n");
		sql.append("       deptparent.code Parent \n");
		sql.append("  FROM org_dept dept  \n");
		sql.append(" LEFT JOIN org_dept deptparent ON dept.pk_fatherorg = deptparent.pk_dept \n");
		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND dept.enablestate = 2 \n");
		sql.append("   AND dept.dr = 0 \n");
		sql.append(" ORDER BY dept.code ASC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}
	public List<Map<String, Object>> querywlData() throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT dept.code BH, \n");
		sql.append("       dept.name TheName, \n");
		sql.append("       dept.mnecode Zjm, \n");
		sql.append("       case when Length(dept.code) = 1 then 1 when Length(dept.code) = 3 then 2 when Length(dept.code) = 5 then 3 else 4 end  Grade, \n");
		sql.append("       CASE WHEN EXISTS (SELECT 1 FROM bd_supplier c WHERE c.pk_supplier_main = dept.pk_supplier) THEN '0' ELSE '1' END IsLeaf, \n");
		sql.append("       '' Parent \n");
		sql.append("  FROM bd_supplier  dept  \n");

		sql.append(" WHERE 1 = 1 \n");
		sql.append("   AND dept.enablestate = 2 \n");
		sql.append("   AND dept.dr = 0 \n");
		sql.append(" ORDER BY dept.code ASC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}
	public List<Map<String, Object>> queryxmData() throws BusinessException {

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT doc.code BH, \n");
		sql.append("       doc.name TheName, \n");
		sql.append("       doc.mnecode Zjm, \n");
		sql.append("       case when Length(doc.code) = 1 then 1 when Length(doc.code) = 3 then 2 when Length(doc.code) = 5 then 3 else 4 end  Grade, \n");
		sql.append("        CASE WHEN EXISTS (SELECT 1 FROM bd_defdoc c WHERE c.pid = doc.pk_defdoc) THEN '0' ELSE '1' END IsLeaf, \n");
		sql.append("       docparent.code Parent \n");
		sql.append("  FROM bd_defdoc doc  \n");
		sql.append("  LEFT JOIN bd_defdoclist doclist  ON doc.pk_defdoclist = doclist.pk_defdoclist\n");
		sql.append(" LEFT JOIN bd_defdoc  docparent ON doc.pid = docparent.pk_defdoc \n");
		sql.append(" WHERE  doclist.code='HRP005'\n");

		sql.append("   AND doc.dr = 0 \n");
		sql.append(" ORDER BY doc.code ASC \n");
		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}
	public List<Map<String, Object>> querygnData() throws BusinessException {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT doc.code BH, \n");
		sql.append("       doc.name TheName, \n");
		sql.append("       doc.mnecode Zjm, \n");
		sql.append("       case when Length(doc.code) = 1 then 1 when Length(doc.code) = 3 then 2 when Length(doc.code) = 5 then 3 else 4 end  Grade, \n");
		sql.append("        CASE WHEN EXISTS (SELECT 1 FROM bd_defdoc c WHERE c.pid = doc.pk_defdoc) THEN '0' ELSE '1' END IsLeaf, \n");
		sql.append("       docparent.code Parent \n");
		sql.append("  FROM bd_defdoc doc  \n");
		sql.append("  LEFT JOIN bd_defdoclist doclist  ON doc.pk_defdoclist = doclist.pk_defdoclist\n");
		sql.append(" LEFT JOIN bd_defdoc  docparent ON doc.pid = docparent.pk_defdoc \n");
		sql.append(" WHERE  doclist.code='HRP002_0xxx'\n");

		sql.append("   AND doc.dr = 0 \n");
		sql.append(" ORDER BY doc.code ASC \n");

		List<Map<String, Object>> sqlResult = (List<Map<String, Object>>) this.getBaseDAO().executeQuery(sql.toString(),
				new MapListProcessor());
		if (CollectionUtils.isEmpty(sqlResult)) {
			return null;
		}

		return sqlResult;
	}

	private BaseDAO baseDAO;

	public BaseDAO getBaseDAO() {
		return baseDAO == null ? new BaseDAO() : baseDAO;
	}

	public IAccountReportWebService getAccountReportWebService() {
		if(null == this.accountReportWebService){
			this.accountReportWebService = NCLocator.getInstance().lookup(IAccountReportWebService.class);
		}
		return this.accountReportWebService;
	}
}